package com.itstyle.quartz.service.mapper;


import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.itstyle.quartz.entity.QuartzEntityBatchDt;
import com.itstyle.quartz.entity.QuartzEntityDt;
import com.itstyle.quartz.entity.QuartzTaskCountDt;
import org.apache.ibatis.annotations.*;
import org.quartz.JobKey;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.List;

/**
 * @author ycj
 * @version 1.0
 * @0
 * @date 2021/1/15 11:28
 */
@Repository
public interface JobMapper extends BaseMapper {

    @Select("select JOB_NAME as JOBNAME,JOB_GROUP AS JOBGROUP,DESCRIPTION,RUNTYPE,KETTLE_PATCH as KETTLEPATCH,KETTLE_DETAILS as KETTLEDETAILS,FILE_PATCH as FILEPATCH from QRTZ_JOB_KETTLE_DETAILS where rownum =1 and JOB_KEY = #{key}")
    List<QuartzEntityDt> list(@Param("key") String key);

    @Select("select t.SOURCE_NAME as SOURCENAME,t.TABLE_NAME AS TABLENAME,t.TARGET_NAME AS TARGETNAME,t.TARGET_TABLE_NAME AS TARGETTABLENAME,t2.KETTLE_PATCH AS KETTLEPATCH from QRTZ_JOB_KETTLE_BATCH t,QRTZ_JOB_KETTLE_DETAILS t2 where t.JOB_KEY=t2.JOB_KEY and t.JOB_KEY=#{key}")
    List<QuartzEntityBatchDt> batchList(@Param("key") String key);

    @Update("update QRTZ_JOB_KETTLE_LOGS set END_TIME=#{endtime},STATUS=#{status},MESSAGE=#{message} where JOB_KEY = #{key} and ID = #{quartzid}")
    int updateMessage(@Param("endtime") String endtime,@Param("status") String status,@Param("message") String message,@Param("key") String key,@Param("quartzid") String quartzid);

    @Select("SELECT t1.tasksum as TASKSUM,t2.runtasksum as RUNTASKSUM,t3.stoptasksum as STOPTASKSUM,t4.errortasksum as ERRORTASKSUM FROM " +
            "(select count(a.JOB_NAME) as tasksum from QRTZ_TRIGGERS a,QRTZ_JOB_KETTLE_DETAILS b where a.JOB_GROUP||'.'||a.JOB_NAME = b.JOB_KEY) t1," +
            "(select count(a.JOB_NAME) as runtasksum from QRTZ_TRIGGERS a,QRTZ_JOB_KETTLE_DETAILS b where a.JOB_GROUP||'.'||a.JOB_NAME = b.JOB_KEY AND a.TRIGGER_STATE IN ('WAITING','ACQUIRED')) t2," +
            "(select count(a.JOB_NAME) as stoptasksum from QRTZ_TRIGGERS a,QRTZ_JOB_KETTLE_DETAILS b where a.JOB_GROUP||'.'||a.JOB_NAME = b.JOB_KEY AND a.TRIGGER_STATE IN ('PAUSED','BLOCKED')) t3," +
            "(select count(a.JOB_NAME) as errortasksum from QRTZ_TRIGGERS a,QRTZ_JOB_KETTLE_DETAILS b where a.JOB_GROUP||'.'||a.JOB_NAME = b.JOB_KEY AND a.TRIGGER_STATE IN ('ERROR')) t4")
    List<QuartzTaskCountDt> jobSum();

    @Select("SELECT JOB_NAME ,JOB_GROUP from QRTZ_JOB_KETTLE_DETAILS where JOB_NAME = #{jobname} or JOB_GROUP = #{jobgroup}")
    List findOne(@Param("jobname") String jobname,@Param("jobgroup") String jobgroup);

    @Delete("delete from \"SHIYJ-1\".\"QRTZ_JOB_KETTLE_DETAILS\" where JOB_KEY = #{key}")
    int deleteJobKettle(@Param("key") String key);

    @Insert("insert into QRTZ_JOB_KETTLE_DETAILS VALUES ( #{jobname},#{jobgroup},#{description},#{runtype},#{kettlepatch},#{parseObject},#{key},#{filepath})")
    int save(@Param("jobname") String jobname,@Param("jobgroup") String jobgroup,@Param("description") String description,@Param("runtype") String runtype,@Param("kettlepatch") String kettlepatch,@Param("parseObject") JSONObject parseObject,@Param("key") String key,@Param("filepath") String filepath);

    @Insert("insert into QRTZ_JOB_KETTLE_LOGS VALUES (#{jobkey},#{kettlepatch},#{begintime},#{endtime},#{status},#{message},#{id})")
    int saveLogs(@Param("jobkey") String jobkey,@Param("kettlepatch") String kettlepatch,@Param("begintime") String begintime,@Param("endtime") String endtime,@Param("status") String status,@Param("message") String message,@Param("id") String id);

    @Select("select owner from SG_DATACENTER.SG_ORG_DCC_B where name like #{cityname}")
    List<String> findOwner(@Param("cityname") String cityname);

    @Select("SELECT * FROM all_tables WHERE  OWNER = #{name} AND TABLE_NAME = #{toUpperCase}")
    List<String> findTable(@Param("name") String name, @Param("toUpperCase") String toUpperCase);

    @Insert("insert into READTABLENAME VALUES (#{filename},#{time})")
    void saveFileName(@Param("filename") String filename,@Param("time") String time);

    @Insert("insert into READERRORTABLENAME VALUES (#{filename},#{time})")
    void saveErrorFileName(@Param("filename") String filename,@Param("time") String time);

    @Select("select TABLE_NAME from READTABLENAME where TABLE_NAME = #{filename}")
    List<String> findFileName(String filename);

    @Select("SELECT COUNT(*) FROM QRTZ_JOB_KETTLE_DETAILS where JOB_KEY = #{key}")
    Long findKey(String key);
}
